/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2008                    */
/* Created on:     2012/8/8 15:19:42                            */
/*==============================================================*/


if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('T_MENU_PRIVILEGE') and o.name = 'FK_T_MENU_P_REFERENCE_T_ROLE')
alter table T_MENU_PRIVILEGE
   drop constraint FK_T_MENU_P_REFERENCE_T_ROLE
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('T_MENU_PRIVILEGE') and o.name = 'FK_T_MENU_P_REFERENCE_T_PRIVIL')
alter table T_MENU_PRIVILEGE
   drop constraint FK_T_MENU_P_REFERENCE_T_PRIVIL
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('T_MENU_PRIVILEGE') and o.name = 'FK_T_MENU_P_REFERENCE_T_SYS_ME')
alter table T_MENU_PRIVILEGE
   drop constraint FK_T_MENU_P_REFERENCE_T_SYS_ME
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('T_USER_ROLE') and o.name = 'FK_T_USER_R_REFERENCE_T_ROLE')
alter table T_USER_ROLE
   drop constraint FK_T_USER_R_REFERENCE_T_ROLE
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('T_USER_ROLE') and o.name = 'FK_T_USER_R_REFERENCE_T_USERS')
alter table T_USER_ROLE
   drop constraint FK_T_USER_R_REFERENCE_T_USERS
go

if exists (select 1
            from  sysobjects
           where  id = object_id('T_MENU_PRIVILEGE')
            and   type = 'U')
   drop table T_MENU_PRIVILEGE
go

if exists (select 1
            from  sysobjects
           where  id = object_id('T_PRIVILEGE')
            and   type = 'U')
   drop table T_PRIVILEGE
go

if exists (select 1
            from  sysobjects
           where  id = object_id('T_ROLE')
            and   type = 'U')
   drop table T_ROLE
go

if exists (select 1
            from  sysobjects
           where  id = object_id('T_SYS_MENU')
            and   type = 'U')
   drop table T_SYS_MENU
go

if exists (select 1
            from  sysobjects
           where  id = object_id('T_USERS')
            and   type = 'U')
   drop table T_USERS
go

if exists (select 1
            from  sysobjects
           where  id = object_id('T_USER_ROLE')
            and   type = 'U')
   drop table T_USER_ROLE
go

/*==============================================================*/
/* Table: T_MENU_PRIVILEGE                                      */
/*==============================================================*/
create table T_MENU_PRIVILEGE (
   PKID                 bigint               not null,
   ROLE_ID              nvarchar(16)         null,
   PRIVILEGE_ID         nchar(3)             null,
   MENU_ID              bigint               null,
   constraint PK_T_MENU_PRIVILEGE primary key (PKID)
)
go

/*==============================================================*/
/* Table: T_PRIVILEGE                                           */
/*==============================================================*/
create table T_PRIVILEGE (
   PRIVILEGE_ID         nchar(3)             not null,
   PRIVILEGE_TYPE       nvarchar(8)          null,
   constraint PK_T_PRIVILEGE primary key (PRIVILEGE_ID)
)
go

/*==============================================================*/
/* Table: T_ROLE                                                */
/*==============================================================*/
create table T_ROLE (
   ROLE_ID              nvarchar(16)         not null,
   ROLE_DESCR           nvarchar(32)         null,
   constraint PK_T_ROLE primary key (ROLE_ID)
)
go

/*==============================================================*/
/* Table: T_SYS_MENU                                            */
/*==============================================================*/
create table T_SYS_MENU (
   MENU_ID              bigint               not null,
   MENU_DESCR           nvarchar(32)         null,
   MENU_URL             nvarchar(255)        null,
   MENU_PID             nvarchar(16)         null,
   constraint PK_T_SYS_MENU primary key (MENU_ID)
)
go

/*==============================================================*/
/* Table: T_USERS                                               */
/*==============================================================*/
create table T_USERS (
   USER_ID              nvarchar(16)         not null,
   PASSWORD             nvarchar(100)        null,
   USER_NAME            nvarchar(16)         null,
   constraint PK_T_USERS primary key (USER_ID)
)
go

/*==============================================================*/
/* Table: T_USER_ROLE                                           */
/*==============================================================*/
create table T_USER_ROLE (
   ROLE_ID              nvarchar(16)         null,
   USER_ID              nvarchar(16)         null
)
go

alter table T_MENU_PRIVILEGE
   add constraint FK_T_MENU_P_REFERENCE_T_ROLE foreign key (ROLE_ID)
      references T_ROLE (ROLE_ID)
go

alter table T_MENU_PRIVILEGE
   add constraint FK_T_MENU_P_REFERENCE_T_PRIVIL foreign key (PRIVILEGE_ID)
      references T_PRIVILEGE (PRIVILEGE_ID)
go

alter table T_MENU_PRIVILEGE
   add constraint FK_T_MENU_P_REFERENCE_T_SYS_ME foreign key (MENU_ID)
      references T_SYS_MENU (MENU_ID)
go

alter table T_USER_ROLE
   add constraint FK_T_USER_R_REFERENCE_T_ROLE foreign key (ROLE_ID)
      references T_ROLE (ROLE_ID)
go

alter table T_USER_ROLE
   add constraint FK_T_USER_R_REFERENCE_T_USERS foreign key (USER_ID)
      references T_USERS (USER_ID)
go

